package com.navi.loaddata.loadService.impl.shtWipLoad;


import com.navi.loaddata.core.gpEntity.WppShtOpeConf;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.LoggerUtil;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})
public class LoadShtWipOpeService {

    private static final Logger logger = LoggerFactory.getLogger(LoadShtWipOpeService.class);

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    public int loadMain1(EtlConfD etlConfD) throws Exception {
        int count=0;
        Session session = gpDao.getEntityManagerSession();
        try {
            long startTimes = System.currentTimeMillis();
            logger.info("Load data  To Tmp start");
            logger.info("etlConfD message : etlconfd startTime: "+etlConfD.getRunStartTimestamp()+
                    "---etlconfd endTime:  "+etlConfD.getRunEndTimestamp() +"---etlconfd ext1"+etlConfD.getExt1());
           count= loadDataToOpeInfoTmp(session, etlConfD);
            long endTimes = System.currentTimeMillis();
            logger.info("Load data  To Tmp finish  ,count:{},{}ms",count,endTimes-startTimes);
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception(e.getMessage());
        }
        return  count;
    }

    public void loadMain2(EtlConfD etlConfD) throws Exception {
        long startTimes = System.currentTimeMillis();
        List<String> tmpList = gpDao.findBySQL(" SELECT d.panel_id FROM stage.wpp_sht_ope_info_tmp2 d where 1=1");
        if(tmpList.size()<=0){
            logger.info("stage.wpp_sht_ope_info_tmp2 is empoty ,job finish!");
            return;
        }
        logger.info("Load Tmp  To  opeInfo start");
        Session session = gpDao.getEntityManagerSession();
        try {
            loadOpeTmpToOpeInfo(session);
            long endTimes = System.currentTimeMillis();
            logger.info("Load Tmp  To  opeInfo end:{}ms ",endTimes-startTimes);

        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception(e.getMessage());
        }
    }

    /**
     *Load OpeInfoTmp
     */
    private int loadDataToOpeInfoTmp(Session session, EtlConfD etlConfD) throws Exception {
        int count=0;
        String tableA = "wpp_asht_ope_his";
        String tableC = "wpp_csht_ope_his";
        String tableF = "wpp_fsht_ope_his";
        count+= loadDataToOpeInfoTmpX(session, etlConfD,"ARRAY", tableA);
        count+=  loadDataToOpeInfoTmpX(session, etlConfD,"OC", tableC);
        count+=  loadDataToOpeInfoTmpX(session, etlConfD,"CF", tableF);
          return  count;
    }
    private  int loadDataToOpeInfoTmpX(Session session, EtlConfD etlConfD,String shopId,String tableName) throws Exception {
        int count;
        try {
            String tmpTableName="wpp_sht_ope_info_tmp2";
            List<WppShtOpeConf> opeConfList = gpDao.list(WppShtOpeConf.class, "from WppShtOpeConf where valigFlg = 'Y' and shopId='" + shopId + "'");
            List<String> opeIds = new ArrayList<>();
            for(WppShtOpeConf obj:opeConfList){
                opeIds.add(obj.getOpeId());
            }
            StringBuffer sqlbuff = new StringBuffer();
            sqlbuff.append(" select H.glass_id as panel_id,H.ope_id,H.logon_timestamp," +
                    "H.logof_timestamp, H.recipe_id ,H.eqpt_id  ");
            sqlbuff.append("  FROM " + tableName + " H  where ");
            sqlbuff.append("   H.DB_TIMESTAMP BETWEEN '" + etlConfD.getRunStartTimestamp() + "' AND '" + etlConfD.getRunEndTimestamp() + "' ");
                sqlbuff.append(" and  H.ope_id in(select ope_id from wpp_sht_ope_conf  where valig_flg='Y'  and shop_id='"+shopId+"')");
            sqlbuff.append(" order by H.glass_id,ope_id ");
            StringBuffer sqlbuffSum = new StringBuffer();
            sqlbuffSum.append(" INSERT INTO STAGE."+tmpTableName+" (panel_id ,ope_id,logon_timestamp,logof_timestamp, recipe_id,eqpt_id)");
            sqlbuffSum.append(sqlbuff);
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuffSum.toString());
            count =   queryLoadStep.executeUpdate();
            logger.info(tableName +"----loadDataToOpeInfoTmpX count :" +count);
            return count;
        }catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception("ERROR loadDataToOpeInfoTmpX :" + e.getMessage());
        }
    }

    /**
     *Load TmpToOpeInfo
     */
    public int loadOpeTmpToOpeInfo(Session session) throws Exception {
        int countSize = 0;
        try {
            List<WppShtOpeConf> opeConfList = gpDao.list(WppShtOpeConf.class, "from WppShtOpeConf where valigFlg = 'Y' ");
            String tableName = "wpp_sht_ope_info";
            empotyTmp(session, "stage.wpp_sht_ope_info_distinct");
            StringBuffer sqlbuffColumn = new StringBuffer();
            long startTimes = System.currentTimeMillis();
            sqlbuffColumn.append(" SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "' " +
                    " ORDER BY ORDINAL_POSITION;");
            List<String> tableColumns = gpDao.findBySQL(sqlbuffColumn.toString());  //获取表中所有列名
            long endTimes = System.currentTimeMillis();
            long minutes = (endTimes - startTimes) ;
            logger.info("Job {}--- loadOpeTmpToOpeInfo  获取 {} 表结构执行了：{} ms ", "LoadShtWipOpeService" ,tableName,minutes);
            loadGpDataToTmp2(session,opeConfList);
            loadTmp2ToDistinct(session,opeConfList);
            distinctToInfo(session,  tableColumns,"wpp_sht_ope_info_distinct","wpp_sht_ope_info");
            List<String> distinct = gpDao.findBySQL(" SELECT d.panel_id FROM stage.wpp_sht_ope_info_distinct d where 1=1");
            if ( distinct.size() > 0) {
                updateWppShtInfoData(session);
                countSize = distinct.size();
                empotyTmp(session, "stage.wpp_sht_ope_info_distinct");
                empotyTmp(session, "stage.wpp_sht_ope_info_tmp2");
                logger.info(" distinct.size() > 0，清空 distinct，tmp");
            }
        } catch (Exception e) {
            throw new Exception("ERROR loadDatOpeTmpToOpeInfo INSERT:" + e.getMessage());
        }
        return countSize;


    }
    private int  loadGpDataToTmp2(Session session,List<WppShtOpeConf> opeConfList) throws Exception{
        int count=0;
        try{
            StringBuffer splitSql = new StringBuffer();
            splitSql.append(" Insert into stage.wpp_sht_ope_info_tmp2 (panel_id,ope_id,eqpt_id,logon_timestamp,logof_timestamp,recipe_id)  select F.panel_id,case F.ope when 'NA'  then  NULL  ");
            opeConfList.forEach(ope -> {
                String opeId = ope.getOpeId();
                String targetSeq = ope.getTargetSeq();
                splitSql.append(" when 'ope"+targetSeq+"'  then  '"+opeId+"'");
            });
            splitSql.append(" else F.ope end as ope_id , ");
            splitSql.append(" F.eqpt_id,F.logon_timestamp,F.logof_timestamp,F.recipe_id from (");
            splitSql.append(" with buffer as(select panel_id,regexp_split_to_table( ");
            for(int i=1;i<=100;i++){
                if(i<10){
                    splitSql.append("coalesce(ope00"+i+",'NA')||'&'||");
                }else if(i>9 && i<=99){
                    splitSql.append("coalesce(ope0"+i+",'NA')||'&'||");
                }else{
                    splitSql.append("coalesce(ope"+i+",'NA')");
                }
            }
            splitSql.append(" ,'&')opevalue from ( select panel_id ");
            for(int i=1;i<=100;i++){
                if(i<10){
                    splitSql.append(",('ope00"+i+",'||coalesce(eqpt_id_00"+i+",'NA')||','||coalesce(move_in_time_00"+i+",null)||','||coalesce(move_out_time_00"+i+",null)||','||coalesce(recipe_id_00"+i+",'NA'))  ope00"+i);
                }else if(i>9 && i<=99){
                    splitSql.append(",('ope0"+i+",'||coalesce(eqpt_id_0"+i+",'NA')||','||coalesce(move_in_time_0"+i+",null)||','||coalesce(move_out_time_0"+i+",null)||','||coalesce(recipe_id_0"+i+",'NA'))  ope0"+i);
                }else{
                    splitSql.append(",('ope"+i+",'||coalesce(eqpt_id_"+i+",'NA')||','||coalesce(move_in_time_"+i+",null)||','||coalesce(move_out_time_"+i+",null)||','||coalesce(recipe_id_"+i+",'NA'))  ope"+i);
                }
            }
            splitSql.append(" from  sor.wpp_sht_ope_info where   panel_id in (select distinct panel_id from stage.wpp_sht_ope_info_tmp2 ) )D)");
            splitSql.append(" select panel_id,split_part(opevalue,',',1) ope,split_part(opevalue,',',2) eqpt_id," +
                    " to_timestamp(split_part(opevalue,',',3) ,'yyyy-mm-dd hh24:mi:ss')  logon_timestamp,to_timestamp(split_part(opevalue,',',4) ,'yyyy-mm-dd hh24:mi:ss')  logof_timestamp," +
                    "          split_part(opevalue,',',5)recipe_id  from buffer )F  where ope <>'NA'");

            SQLQuery queryLoadStep = session.createSQLQuery(splitSql.toString());
            count =  queryLoadStep.executeUpdate();
            logger.info("----loadGpDataToTmp2() count :" +count);
        }catch (Exception e){
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception("ERROR loadGpDataToTmp2() :" + e);
        }
        return count;
    }
    private int  loadTmp2ToDistinct(Session session,List<WppShtOpeConf> opeConfList) throws Exception{
        int count=0;
        StringBuffer sql=new StringBuffer();
        try{
            sql.append(" (select tmp2.panel_id,tmp2.ope_id,tmp2.eqpt_id,tmp2.logon_timestamp, tmp2.logof_timestamp ,tmp2.recipe_id ");
            sql.append("  from stage.wpp_sht_ope_info_tmp2  tmp2 ,");
            sql.append(" (select panel_id,ope_id,max(logof_timestamp) as logof from stage.wpp_sht_ope_info_tmp2   group by panel_id,ope_id)T");
            sql.append(" where tmp2.panel_id=T.panel_id and tmp2.ope_id =T.ope_id and  tmp2.logof_timestamp =T.logof ) ");
             List<String> tableColumns = new ArrayList<>();
            StringBuffer sqlbuff = new StringBuffer();
            sqlbuff.append(" select H.panel_id ");
            //行转列
            opeConfList.forEach(ope -> {
                String opeId = ope.getOpeId();
                String targetSeq = ope.getTargetSeq();
                sqlbuff.append(" ,max(case when H.ope_id = '" + opeId + "' then H.logon_timestamp else null end) as move_in_time_" + targetSeq);
                sqlbuff.append(" ,max(case when H.ope_id = '" + opeId + "' then H.logof_timestamp else null end) as move_out_time_" + targetSeq);
                sqlbuff.append(" ,max(case when H.ope_id = '" + opeId + "' then H.recipe_id else null end) as recipe_id_" + targetSeq);
                sqlbuff.append(" ,max(case when H.ope_id = '" + opeId + "' then H.eqpt_id else null end) as eqpt_id_" + targetSeq);
                tableColumns.add("move_in_time_" + targetSeq);
                tableColumns.add("move_out_time_" + targetSeq);
                tableColumns.add("recipe_id_" + targetSeq);
                tableColumns.add("eqpt_id_" + targetSeq);
            });
            sqlbuff.append("  FROM " + sql.toString() + "H  ");
            sqlbuff.append(" group by H.panel_id ");
            StringBuffer sqlbuffSum = new StringBuffer();
            sqlbuffSum.append(" INSERT INTO STAGE.wpp_sht_ope_info_distinct (panel_id  ");
            //获取所有列
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffSum.append("," + column);
            });

            sqlbuffSum.append(")").append(sqlbuff);
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuffSum.toString());
            count =   queryLoadStep.executeUpdate();
            logger.info("----loadTmp2ToDistinct() count :" +count);
        }catch (Exception e){
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception("ERROR loadTmp2ToDistinct() :" + e);
        }
        return count;
    }
    private void distinctToInfo(Session session, List<String> tableColumns,String distinctTableName,String finalTableName) throws Exception {
        // mergeTableName ="WPP_SHT_INFO";
        //finalTableName ="WPP_SHT_INFO_MERGE";
        String sqlDelete = " DELETE FROM "+finalTableName+"  I USING STAGE."+distinctTableName+" AS M  WHERE I.PANEL_ID=M.PANEL_ID ";
        StringBuffer sqlbuffInfo = new StringBuffer();
        try {
            // DELETE USING，删除全量表的符合条件的记录。
            SQLQuery queryLoadStepD = session.createSQLQuery(sqlDelete);
            queryLoadStepD.executeUpdate();
            //  INSERT wpp_sht_info

            sqlbuffInfo.append(" INSERT INTO "+finalTableName+" (panel_id");
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffInfo.append("," + column);
            });
            sqlbuffInfo.append(" ) ");
            sqlbuffInfo.append("  SELECT panel_id");
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffInfo.append("," + column);
            });
            sqlbuffInfo.append("  FROM STAGE."+distinctTableName);
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuffInfo.toString());
            int count =   queryLoadStep.executeUpdate();
            logger.info(distinctTableName+"  To  "+finalTableName+" count :" +count);
        } catch (Exception e) {
            logger.error("************   sqlDelete.toString():  **********    "+sqlDelete.toString());
            logger.error("************   sqlbuffInfo.toString():  **********    "+sqlbuffInfo.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception("ERROR distinctToInfo :" + e.getMessage());
        }


    }


    /**
     *    对 wpp_sht_ope_info  表进行大板，小板 数据互补
     */
    private void updateWppShtInfoData(Session session)throws Exception{
        StringBuffer sqlbuff = new StringBuffer();
        try {
//用ARRAY补全wpp_sht_ope_info  OC数据
            String sqlAdd="";
            sqlbuff.append(" update wpp_sht_ope_info o set    ");
            for(int i=1;i<=100;i++){
                String tag;
                if(i<10){
                    tag= ""+"00"+i;
                }else if(i>=10 && i<100){
                    tag= ""+"0"+i;
                }else{
                    tag= ""+i;
                }
                sqlbuff.append(" eqpt_id_"+tag+"           =case when a.eqpt_id_"+tag+"$           is not null then a.eqpt_id_"+tag+"$            else eqpt_id_"+tag+" end, ");
                sqlbuff.append(" move_in_time_"+tag+"      =case when a.move_in_time_"+tag+"$      is not null then a.move_in_time_"+tag+"$       else move_in_time_"+tag+" end, ");
                sqlbuff.append(" recipe_id_"+tag+"         =case when a.recipe_id_"+tag+"$         is not null then a.recipe_id_"+tag+"$          else recipe_id_"+tag+" end, ");
                sqlbuff.append(" move_out_time_"+tag+"     =case when a.move_out_time_"+tag+"$     is not null then a.move_out_time_"+tag+"$      else move_out_time_"+tag+" end,");
                sqlAdd+=" ,h.eqpt_id_"+tag+" as eqpt_id_"+tag+"$,h.move_in_time_"+tag+" as move_in_time_"+tag+"$," +
                        " h.recipe_id_"+tag+" as recipe_id_"+tag+"$,h.move_out_time_"+tag+" as move_out_time_"+tag+"$  ";
            }
            sqlbuff.deleteCharAt(sqlbuff.length()-1);
            sqlbuff.append(" from( select i.*,h.panel_id ");
            //sqlbuff.append(" ,h.eqpt_id_007 as eqpt_id_007$,h.move_in_time_007 as move_in_time_007$ ");
            sqlbuff.append(sqlAdd);
            sqlbuff.append(" from (select panel_id as panel,tft_glass_id,cf_glass_id from wpp_sht_info )i ");
            sqlbuff.append(" left join (select * from wpp_sht_ope_info where panel_id in ");
            sqlbuff.append("  (select glass_id from  wpp_asht_ope_his where glass_id in(select panel_id from stage.wpp_sht_ope_info_distinct))  )h  on ");
            sqlbuff.append(" i.tft_glass_id =h.panel_id where panel_id is not null and panel_id<>panel  ");
            sqlbuff.append(" and length(panel)>10 )a where  o.panel_id =a.panel; ");
//用CF补全wpp_sht_ope_info  OC数据
            String sqlFdd="";
            sqlbuff.append(" update wpp_sht_ope_info o set    ");
            for(int i=1;i<=100;i++){
                String tag;
                if(i<10){
                    tag= ""+"00"+i;
                }else if(i>=10 && i<100){
                    tag= ""+"0"+i;
                }else{
                    tag= ""+i;
                }
                sqlbuff.append(" eqpt_id_"+tag+"      =case when eqpt_id_"+tag+"      is null then a.eqpt_id_"+tag+"$       else eqpt_id_"+tag+" end, ");
                sqlbuff.append(" move_in_time_"+tag+"      =case when move_in_time_"+tag+"      is null then a.move_in_time_"+tag+"$       else move_in_time_"+tag+" end, ");
                sqlbuff.append(" recipe_id_"+tag+"     =case when recipe_id_"+tag+"      is null then a.recipe_id_"+tag+"$       else recipe_id_"+tag+" end, ");
                sqlbuff.append(" move_out_time_"+tag+"     =case when move_out_time_"+tag+"      is null then a.move_out_time_"+tag+"$       else move_out_time_"+tag+" end,");
                sqlFdd+=" ,h.eqpt_id_"+tag+" as eqpt_id_"+tag+"$,h.move_in_time_"+tag+" as move_in_time_"+tag+"$," +
                        " h.recipe_id_"+tag+" as recipe_id_"+tag+"$,h.move_out_time_"+tag+" as move_out_time_"+tag+"$  ";
            }
            sqlbuff.deleteCharAt(sqlbuff.length()-1);
            sqlbuff.append(" from( select i.*,h.panel_id ");
            //sqlbuff.append(" ,h.eqpt_id_007 as eqpt_id_007$,h.move_in_time_007 as move_in_time_007$ ");
            sqlbuff.append(sqlFdd);
            sqlbuff.append(" from (select panel_id as panel,tft_glass_id,cf_glass_id from wpp_sht_info )i ");
            sqlbuff.append(" left join (select * from wpp_sht_ope_info where panel_id in ");
            sqlbuff.append("  (select glass_id from  wpp_fsht_ope_his where glass_id in(select panel_id from stage.wpp_sht_ope_info_distinct))  )h  on ");
            sqlbuff.append("i.cf_glass_id =h.panel_id where panel_id is not null and panel_id<>panel  ");
            sqlbuff.append(" and length(panel)>10 )a where  o.panel_id =a.panel; ");

            long startTimes = System.currentTimeMillis();
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuff.toString());
            int count =   queryLoadStep.executeUpdate();
            long endTimes = System.currentTimeMillis();
            long minutes = (endTimes - startTimes) / 60;
            logger.info("用CF大板补全ARRAY,OC板中CF数据;用 ARRAY大板数据  补全OC，CF板的ARRAY数据;  minutes:{}s---count{}:", minutes,count);
        } catch (Exception e) {
            logger.error("************  updateWppShtOpeData SQL:  **********    "+sqlbuff.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception("ERROR updateWppShtOpeData :" + e.getMessage());
        }
    }


    //清空临时表
    private void empotyTmp(Session session, String tableName) throws Exception {
        StringBuffer sqlbuff = new StringBuffer();
        try {
            sqlbuff.append(" TRUNCATE TABLE " + tableName);
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuff.toString());
            int count =   queryLoadStep.executeUpdate();
            logger.info("TRUNCATE table :"+tableName+"   count :" +count);
        } catch (Exception e) {
            logger.error("************   empotyTmp.toString():  **********    "+sqlbuff.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new Exception("ERROR TRUNCATE TABLE " + tableName+" :" + e.getMessage());
        }
    }






















}
